"""
Case Type   : ustore_autovacuum
Case Name   : autovacuum_mode相关测试--清理模式为none
Create At   : 2022/01/17
Owner       : zhao-pengcheng-g
Description :
    1.设置清理模式为none
    2.执行DML语句
    3.查询last_autovacuum_time,last_autoanalyze_time
    4.清理资源，恢复参数
Expect      :
    1.设置成功
    2.执行DML语句成功
    3.查询成功，且数据正常
    4.清理资源成功，恢复参数成功
History     :
    Modifide by li-xin12345 at 2024.10.31：优化环境清理
"""
import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from testcase.utils.Common import Common


class Ustore_autovacuum_mode(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        text = '---Opengauss_Function_Ustore_Autovacuum_Case0019 start---'
        self.logger.info(text)
        self.userNode = Node('PrimaryDbUser')
        self.sh_primy = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.oid = 0

        text = '---查询参数默认值，修改参数，expect：成功---'
        self.logger.info(text)
        self.item_mode = 'autovacuum_mode'
        self.item_naptime = 'autovacuum_naptime'
        self.item_autovacuum = 'autovacuum'
        self.show_mode_cmd = f'show {self.item_mode};'
        self.show_naptime_cmd = f'show {self.item_naptime};'
        self.show_autovacuum_cmd = f'show {self.item_autovacuum};'
        show_mode_res = self.sh_primy.execut_db_sql(self.show_mode_cmd)
        self.logger.info(show_mode_res)
        self.default_mode = show_mode_res.splitlines()[-2].strip()
        show_naptime_res = self.sh_primy.execut_db_sql(self.show_naptime_cmd)
        self.logger.info(show_naptime_res)
        self.default_naptime = show_naptime_res.splitlines()[-2].strip()
        show_autovacuum_res = \
            self.sh_primy.execut_db_sql(self.show_autovacuum_cmd)
        self.logger.info(show_autovacuum_res)
        self.autovacuum_default = show_autovacuum_res.splitlines()[-2].strip()
        if 'on' != self.autovacuum_default:
            result = self.sh_primy.execute_gsguc('reload',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            f'{self.item_autovacuum}=on')
            self.assertTrue(result, '参数修改失败')
        check_msg = self.sh_primy.execut_db_sql(self.show_autovacuum_cmd)
        self.logger.info(check_msg)
        self.common.equal_sql_mdg(check_msg, f'{self.item_autovacuum}',
                                  'on', '(1 row)', flag='1')
        text = '---创建存储过程,expect:成功---'
        self.logger.info(text)
        sql_cmd_1 = f"create or replace procedure init_autovacuum" \
            f"(tname varchar ,a int,b int) as sqlst varchar(500);" \
            f"j int;" \
            f"begin " \
            f"for i in a..b loop " \
            f"j := i+521;" \
            f"sqlst := 'insert into '|| tname ||' values ('||i||','||j||',''abcddeg'')';" \
            f"execute immediate sqlst;" \
            f"end loop;" \
            f"end;"
        self.logger.info(sql_cmd_1)
        sql_msg_1 = self.sh_primy.execut_db_sql(sql_cmd_1)
        self.logger.info(sql_msg_1)
        self.assertIn(self.constant.CREATE_PROCEDURE_SUCCESS_MSG,
                      sql_msg_1, '执行失败' + text)
        text = '---创建测试表,expect:成功---'
        self.logger.info(text)
        sql_cmd_2 = f'''drop table if exists test_autovacuum_1;
                        create table test_autovacuum_1 (a int, b number, \
                        c varchar2(15)) WITH (STORAGE_TYPE=USTORE);
                        call init_autovacuum('test_autovacuum_1', 0, 10000);'''
        self.logger.info(sql_cmd_2)
        sql_msg_2 = self.sh_primy.execut_db_sql(sql_cmd_2)
        self.logger.info(sql_msg_2)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS and 'init_autovacuum',
                      sql_msg_2, '执行失败' + text)

    def test_autovacuum_mode(self):
        text = '---step1:设置清理模式为analyze，expect:设置成功---'
        self.logger.info(text)
        guc_mode = self.sh_primy.execute_gsguc('reload',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            f'{self.item_mode} = none',
                                            single=True)
        self.assertTrue(guc_mode, '执行失败:' + text)
        show_mode_msg = self.sh_primy.execut_db_sql(self.show_mode_cmd)
        self.logger.info(show_mode_msg)
        self.assertIn('none', show_mode_msg, '执行失败:' + text)
        guc_naptime = self.sh_primy.execute_gsguc('reload',
                                               self.constant.GSGUC_SUCCESS_MSG,
                                               'autovacuum_naptime = 30s',
                                               single=True)
        self.assertTrue(guc_naptime, '执行失败:' + text)
        show_naptime_msg = self.sh_primy.execut_db_sql(self.show_naptime_cmd)
        self.logger.info(show_naptime_msg)
        self.assertIn('30s', show_naptime_msg, '执行失败:' + text)

        text = '---step2:执行DML语句，expect:成功---'
        self.logger.info(text)
        sql_cmd = f'''select oid from pg_class \
                  where relname = 'test_autovacuum_1';
                  update test_autovacuum_1 set a = 555 \
                  where a > 2100 and a< 4000;
                  delete from test_autovacuum_1 where a > 8500;
                  select pg_sleep(60);'''
        sql_exec_msg = self.sh_primy.execut_db_sql(sql_cmd)
        self.logger.info(sql_exec_msg)
        self.oid = sql_exec_msg.splitlines()[2].strip()
        self.assertNotEqual(self.oid, '0', '执行失败:' + text)
        self.assertIn(self.constant.UPDATE_SUCCESS_MSG and
                      self.constant.DELETE_SUCCESS_MSG and
                      'pg_sleep', sql_exec_msg, '执行失败:' + text)

        text = '---step3:查询last_autovacuum_time,last_autoanalyze_time,' \
               'expect:查询成功，且数据正常---'
        self.logger.info(text)
        sql_cmd = f'''select pg_stat_get_last_autovacuum_time({self.oid});
                      select pg_stat_get_last_autoanalyze_time({self.oid});'''
        sql_exec_msg = self.sh_primy.execut_db_sql(sql_cmd)
        self.logger.info(sql_exec_msg)
        vacuum_time = sql_exec_msg.splitlines()[2].strip()
        analyze_time = sql_exec_msg.splitlines()[-2].strip()
        date_cmd = f"select current_date;"
        date_msg = self.sh_primy.execut_db_sql(date_cmd)
        self.logger.info(date_msg)
        current_date = date_msg.splitlines()[-2].strip()
        self.assertNotIn(current_date[:10], analyze_time, '执行失败:' + text)
        self.assertNotIn(current_date[:10], vacuum_time, '执行失败:' + text)

    def tearDown(self):
        text = '---step4:资源清理，恢复参数，expect:成功---'
        self.logger.info(text)
        sql_cmd = f'drop procedure init_autovacuum;' \
                  f'drop table test_autovacuum_1;'
        self.logger.info(sql_cmd)
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd)
        self.logger.info(sql_msg)

        check_res = self.sh_primy.execut_db_sql(self.show_autovacuum_cmd)
        self.logger.info(check_res)
        check_current = check_res.splitlines()[-2].strip()
        if check_current != self.autovacuum_default:
            result = self.sh_primy.execute_gsguc('reload',
                        self.constant.GSGUC_SUCCESS_MSG,
                        f'{self.item_autovacuum}={self.autovacuum_default}')
            self.assertTrue(result, '参数修改失败')
        check_msg = self.sh_primy.execut_db_sql(self.show_autovacuum_cmd)
        self.logger.info(check_msg)
        check_mode_res = self.sh_primy.execut_db_sql(self.show_mode_cmd)
        self.logger.info(check_mode_res)
        current_mode = check_mode_res.splitlines()[-2].strip()
        if self.default_mode != current_mode:
            guc_result = self.sh_primy.execute_gsguc('reload',
                            self.constant.GSGUC_SUCCESS_MSG,
                            f'{self.item_mode} = {self.default_mode}',
                            single=True)
            self.assertTrue(guc_result, '执行失败:' + text)
        show_mode_msg = self.sh_primy.execut_db_sql(self.show_mode_cmd)
        self.logger.info(show_mode_msg)
        check_naptime_res = self.sh_primy.execut_db_sql(self.show_naptime_cmd)
        self.logger.info(check_naptime_res)
        current_naptime = check_naptime_res.splitlines()[-2].strip()
        if self.default_naptime != current_naptime:
            guc_result = self.sh_primy.execute_gsguc('reload',
                            self.constant.GSGUC_SUCCESS_MSG,
                            f'{self.item_naptime} = {self.default_naptime}',
                            single=True)
            self.assertTrue(guc_result, '执行失败:' + text)
        show_naptime_msg = self.sh_primy.execut_db_sql(self.show_naptime_cmd)
        self.logger.info(show_naptime_msg)
        self.assertIn(self.constant.DROP_PROCEDURE_SUCCESS_MSG and
                      self.constant.DROP_TABLE_SUCCESS,
                      sql_msg, '执行失败:' + text)
        self.common.equal_sql_mdg(check_msg, self.item_autovacuum,
                        self.autovacuum_default, '(1 row)', flag='1')
        self.assertIn(self.default_mode, show_mode_msg, '执行失败:' + text)
        self.assertIn(self.default_naptime, show_naptime_msg,
                      '执行失败:' + text)
        self.logger.info(
            '---Opengauss_Function_Ustore_Autovacuum_Case0019 finish---')